﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;
using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataTransferObject.MetaDataList;
using DataAccessLayer;
using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.ListSalesmenCategoryDTSTableAdapters;

namespace BusinessLogicLayer.MetaDataList
{
    public class ListSalesmenCategoryBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.ListSalesmenCategory; } }

        private UserInformation userOrganization;

        private ListSalesmenCategoryMaster listSalesmenCategoryMaster;

        private BindingList<ListSalesmenCategoryDetail> listSalesmenCategoryDetailList;



        public ListSalesmenCategoryBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                listSalesmenCategoryMaster = new ListSalesmenCategoryMaster();

                this.listSalesmenCategoryDetailList = new BindingList<ListSalesmenCategoryDetail>();

                GlobalDefaultValue.Apply(this);


                this.ListSalesmenCategoryMaster.PropertyChanged += new PropertyChangedEventHandler(ListSalesmenCategoryMaster_PropertyChanged);

                this.ListSalesmenCategoryDetailList.ListChanged += new ListChangedEventHandler(ListSalesmenCategoryDetailList_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void ListSalesmenCategoryMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void ListSalesmenCategoryDetailList_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private ListSalesmenCategoryListingTableAdapter listingTableAdapter;
        protected ListSalesmenCategoryListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new ListSalesmenCategoryListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private ListSalesmenCategoryTableAdapter masterTableAdapter;
        protected ListSalesmenCategoryTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new ListSalesmenCategoryTableAdapter();
                return masterTableAdapter;
            }
        }

        private ListSalesmenCategoryDetailTableAdapter detailTableAdapter;
        protected ListSalesmenCategoryDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new ListSalesmenCategoryDetailTableAdapter();
                return detailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public ListSalesmenCategoryDTS.ListSalesmenCategoryListingDataTable ListSalesmenCategoryListing()
        {
            return this.ListingTableAdapter.GetData();
        }

        public ListSalesmenCategoryMaster ListSalesmenCategoryMaster
        {
            get { return this.listSalesmenCategoryMaster; }
        }

        public BindingList<ListSalesmenCategoryDetail> ListSalesmenCategoryDetailList
        {
            get { return this.listSalesmenCategoryDetailList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int SalesmenCategoryID   //Primary Key
        {
            get { return this.ListSalesmenCategoryMaster.SalesmenCategoryID; }
            private set
            {
                if (this.ListSalesmenCategoryMaster.SalesmenCategoryID != value)
                {
                    this.StopTracking();

                    this.ListSalesmenCategoryMaster.SalesmenCategoryID = value;

                    this.ListSalesmenCategoryGetMaster();
                    this.ListSalesmenCategoryGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void ListSalesmenCategoryGetMaster()
        {
            if (this.SalesmenCategoryID > 0)
            {
                ListSalesmenCategoryDTS.ListSalesmenCategoryDataTable masterDataTable = this.MasterTableAdapter.GetData(this.SalesmenCategoryID);

                if (masterDataTable.Count > 0)
                {
                    this.ListSalesmenCategoryMaster.StopTracking();

                    this.ListSalesmenCategoryMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.ListSalesmenCategoryMaster.AncestorID = masterDataTable[0].AncestorID;
                    this.ListSalesmenCategoryMaster.SalesmenID = masterDataTable[0].SalesmenID;

                    this.ListSalesmenCategoryMaster.Description = masterDataTable[0].Description;
                    this.ListSalesmenCategoryMaster.Other = masterDataTable[0].Other;

                    this.ListSalesmenCategoryMaster.InActive = masterDataTable[0].InActive;

                    this.ListSalesmenCategoryMaster.StartTracking();

                    this.ListSalesmenCategoryMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.ListSalesmenCategoryMaster);
                this.ListSalesmenCategoryMaster.EntryDate = DateTime.Today;
                this.ListSalesmenCategoryMaster.Reset();
            }
        }


        private void ListSalesmenCategoryGetDetail()
        {
            this.listSalesmenCategoryDetailList.RaiseListChangedEvents = false;
            this.listSalesmenCategoryDetailList.Clear();
            if (this.SalesmenCategoryID >= 0)
            {

                ListSalesmenCategoryDTS.ListSalesmenCategoryDetailDataTable detailDataTable = this.DetailTableAdapter.GetData(this.SalesmenCategoryID);

                if (detailDataTable.Count > 0)
                {
                    foreach (ListSalesmenCategoryDTS.ListSalesmenCategoryDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.listSalesmenCategoryDetailList.Add(new ListSalesmenCategoryDetail(detailRow.CommonID, detailRow.CommonValue, detailRow.Remarks));
                    }
                }
            }
            this.listSalesmenCategoryDetailList.RaiseListChangedEvents = true;
            this.listSalesmenCategoryDetailList.ResetBindings();
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<ListSalesmenCategoryDetail> inValidListSalesmenCategoryDetail = this.ListSalesmenCategoryDetailList.Where(listSalesmenCategoryDetail => !listSalesmenCategoryDetail.IsValid).ToList();
                return this.ListSalesmenCategoryMaster.IsValid && inValidListSalesmenCategoryDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListSalesmenCategoryEditable", this.SalesmenCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListSalesmenCategoryVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListSalesmenCategoryEditable", this.SalesmenCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListSalesmenCategoryUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListSalesmenCategoryEditable", this.SalesmenCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int salesmenCategoryID)
        {
            if (this.SalesmenCategoryID == salesmenCategoryID) this.SalesmenCategoryID = -1; //Enforce to reload
            this.SalesmenCategoryID = salesmenCategoryID;
        }

        public void New()
        {
            if (this.SalesmenCategoryID == 0) this.SalesmenCategoryID = -1;
            this.SalesmenCategoryID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method


        public bool Save()
        {
            int salesmenCategoryID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref salesmenCategoryID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(salesmenCategoryID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    transactionScope.Complete();
                }

                SQLDatabase.ExecuteNonQuery("ListSalesmenCategoryUpdateHierarchyLevel", CommandType.StoredProcedure, "AncestorID", this.listSalesmenCategoryMaster.AncestorID);

                this.Fill(salesmenCategoryID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.listSalesmenCategoryMaster.SalesmenCategoryID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.listSalesmenCategoryMaster.SalesmenCategoryID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.listSalesmenCategoryMaster.SalesmenCategoryID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    transactionScope.Complete();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.ListSalesmenCategoryMaster.EntryDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int salesmenCategoryID)
        {
            ListSalesmenCategoryDTS.ListSalesmenCategoryDataTable masterDataTable;
            ListSalesmenCategoryDTS.ListSalesmenCategoryRow masterRow;

            if (this.listSalesmenCategoryMaster.SalesmenCategoryID <= 0) //Add
            {
                masterDataTable = new ListSalesmenCategoryDTS.ListSalesmenCategoryDataTable();
                masterRow = masterDataTable.NewListSalesmenCategoryRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(listSalesmenCategoryMaster.SalesmenCategoryID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(listSalesmenCategoryMaster.SalesmenCategoryID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.EntryDate = this.listSalesmenCategoryMaster.EntryDate;

            masterRow.AncestorID = this.listSalesmenCategoryMaster.AncestorID;
            masterRow.HierarchyLevel = 0;
            masterRow.HierarchyOrder = "";

            masterRow.SalesmenID = this.listSalesmenCategoryMaster.SalesmenID;

            masterRow.Description = this.listSalesmenCategoryMaster.Description;
            masterRow.Other = this.listSalesmenCategoryMaster.Other;

            masterRow.InActive = false;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.listSalesmenCategoryMaster.SalesmenCategoryID <= 0) masterDataTable.AddListSalesmenCategoryRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            salesmenCategoryID = masterRow.SalesmenCategoryID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int salesmenCategoryID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save collection>

            serialID = 0;

            ListSalesmenCategoryDTS.ListSalesmenCategoryDetailDataTable detailDataTable = new ListSalesmenCategoryDTS.ListSalesmenCategoryDetailDataTable();

            foreach (ListSalesmenCategoryDetail listSalesmenCategoryDetail in this.ListSalesmenCategoryDetailList)
            {
                ListSalesmenCategoryDTS.ListSalesmenCategoryDetailRow detailRow = detailDataTable.NewListSalesmenCategoryDetailRow();

                detailRow.SalesmenCategoryID = salesmenCategoryID;
                detailRow.SerialID = ++serialID;

                detailRow.CommonID = listSalesmenCategoryDetail.CommonID;
                detailRow.CommonValue = listSalesmenCategoryDetail.CommonValue;

                detailRow.Remarks = listSalesmenCategoryDetail.Remarks;

                detailDataTable.AddListSalesmenCategoryDetailRow(detailRow);
            }

            rowsAffected = this.DetailTableAdapter.Update(detailDataTable);
            if (rowsAffected != this.listSalesmenCategoryDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail");


            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int salesmenCategoryID)
        {
            this.DetailTableAdapter.Delete(salesmenCategoryID);

            return true;
        }



        #endregion



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;



            queryString = "     " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      SalesmenCategoryID, Description, AncestorID, Other, EntryDate " + "\r\n";
            queryString = queryString + "       FROM        ListSalesmenCategory " + "\r\n";
            queryString = queryString + "       ORDER BY    Description " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryListing", queryString);



            queryString = "     @SalesmenCategoryID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      SalesmenCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID " + "\r\n";
            queryString = queryString + "       FROM        ListSalesmenCategory " + "\r\n";
            queryString = queryString + "       WHERE       SalesmenCategoryID = @SalesmenCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategorySelect", queryString);


            queryString = "     @Description nvarchar(100),	@AncestorID int, @HierarchyLevel int, @HierarchyOrder nvarchar(50), @SalesmenID int, @Other nvarchar(100), @InActive bit, @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListSalesmenCategory (Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID) VALUES (@Description, @AncestorID, @HierarchyLevel, @HierarchyOrder, @SalesmenID, @Other, @InActive, @EntryDate, @UserID, @UserOrganizationID) " + "\r\n";
            queryString = queryString + "       SELECT      SalesmenCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID FROM ListSalesmenCategory WHERE SalesmenCategoryID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryInsert", queryString);


            queryString = "     @SalesmenCategoryID int, @Description nvarchar(100), @AncestorID int, @HierarchyLevel int, @HierarchyOrder nvarchar(50), @SalesmenID int, @Other nvarchar(100), @InActive bit, @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListSalesmenCategory SET Description = @Description, AncestorID = @AncestorID, HierarchyLevel = @HierarchyLevel, HierarchyOrder = @HierarchyOrder, SalesmenID = @SalesmenID, Other = @Other, InActive = @InActive, EntryDate = @EntryDate, UserID = @UserID, UserOrganizationID = @UserOrganizationID WHERE SalesmenCategoryID = @SalesmenCategoryID " + "\r\n";
            queryString = queryString + "       SELECT      SalesmenCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID FROM ListSalesmenCategory WHERE SalesmenCategoryID = @SalesmenCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryUpdate", queryString);


            queryString = " @SalesmenCategoryID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListSalesmenCategory WHERE SalesmenCategoryID = @SalesmenCategoryID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryDelete", queryString);






            queryString = " @SalesmenCategoryID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      SalesmenCategoryID, SerialID, CommonID, CommonValue, Remarks " + "\r\n";
            queryString = queryString + "       FROM        ListSalesmenCategoryDetail " + "\r\n";
            queryString = queryString + "       WHERE       SalesmenCategoryID = @SalesmenCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryDetailSelect", queryString);


            queryString = " @SalesmenCategoryID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListSalesmenCategoryDetail (SalesmenCategoryID, SerialID, CommonID, CommonValue, Remarks) VALUES (@SalesmenCategoryID, @SerialID, @CommonID, @CommonValue, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT      SalesmenCategoryID, SerialID, CommonID, CommonValue, Remarks FROM ListSalesmenCategoryDetail WHERE (SalesmenCategoryID = @SalesmenCategoryID) AND (SerialID = @SerialID) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryDetailInsert", queryString);



            queryString = " @SalesmenCategoryID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListSalesmenCategoryDetail SET CommonID = @CommonID, CommonValue = @CommonValue, Remarks = @Remarks WHERE SalesmenCategoryID = @SalesmenCategoryID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      SalesmenCategoryID, SerialID, CommonID, CommonValue, Remarks FROM ListSalesmenCategoryDetail WHERE SalesmenCategoryID = @SalesmenCategoryID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryDetailUpdate", queryString);



            queryString = " @SalesmenCategoryID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListSalesmenCategoryDetail WHERE SalesmenCategoryID = @SalesmenCategoryID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryDetailDelete", queryString);



            queryString = " @AncestorID Int ";
            queryString = queryString + " WITH ENCRYPTION ";
            queryString = queryString + " AS ";

            queryString = queryString + "   DECLARE @AncestorHierarchyOrder NVarchar(30), @AncestorHierarchyLevel Int  ";
            queryString = queryString + "   DECLARE @Cursor_Rows Int, @SerialNo Int ";

            queryString = queryString + "   SET @AncestorHierarchyOrder = N'' ";
            queryString = queryString + "   SET @AncestorHierarchyLevel = 0 ";
            queryString = queryString + "   SET @SerialNo = 0 ";
            queryString = queryString + "   SET @Cursor_Rows = 0 ";

            queryString = queryString + "   IF @AncestorID <> 0 ";
            queryString = queryString + "   BEGIN ";

            queryString = queryString + "       DECLARE Ancestor_Cursor CURSOR LOCAL FOR ";
            queryString = queryString + "       SELECT TOP 1 HierarchyOrder, HierarchyLevel FROM ListSalesmenCategory ";
            queryString = queryString + "       WHERE SalesmenCategoryID = @AncestorID ";

            queryString = queryString + "       OPEN Ancestor_Cursor ";

            queryString = queryString + "       FETCH NEXT FROM Ancestor_Cursor INTO @AncestorHierarchyOrder, @AncestorHierarchyLevel ";

            queryString = queryString + "       CLOSE Ancestor_Cursor ";
            queryString = queryString + "       DEALLOCATE Ancestor_Cursor ";


            queryString = queryString + "   END ";

            queryString = queryString + "   DECLARE @SalesmenCategoryID Int ";

            queryString = queryString + "   DECLARE SalesmenCategory_Cursor CURSOR LOCAL SCROLL FOR ";
            queryString = queryString + "   SELECT SalesmenCategoryID FROM ListSalesmenCategory ";
            queryString = queryString + "   WHERE AncestorID = @AncestorID ORDER BY Description ";
            queryString = queryString + "   FOR UPDATE ";

            queryString = queryString + "   OPEN SalesmenCategory_Cursor ";

            queryString = queryString + "   FETCH LAST FROM SalesmenCategory_Cursor INTO @SalesmenCategoryID ";
            queryString = queryString + "   IF @@FETCH_STATUS = 0 ";
            queryString = queryString + "   BEGIN ";
            queryString = queryString + "       SET @Cursor_Rows = @@CURSOR_ROWS ";
            queryString = queryString + "       FETCH FIRST FROM SalesmenCategory_Cursor INTO @SalesmenCategoryID ";
            queryString = queryString + "   END ";

            queryString = queryString + "   WHILE @@FETCH_STATUS = 0";
            queryString = queryString + "   BEGIN";

            queryString = queryString + "       SET @SerialNo = @SerialNo + 1 ";

            queryString = queryString + "       UPDATE ListSalesmenCategory SET HierarchyOrder = @AncestorHierarchyOrder +  REPLICATE('0', DATALENGTH(CONVERT (NVarchar(30), @Cursor_Rows)) - DATALENGTH(CONVERT (NVarchar(30), @SerialNo))) + CONVERT (NVarchar(30), @SerialNo), HierarchyLevel = @AncestorHierarchyLevel + 1 ";
            queryString = queryString + "       WHERE CURRENT OF SalesmenCategory_Cursor ";

            queryString = queryString + "       EXECUTE ListSalesmenCategoryUpdateHierarchyLevel @AncestorID = @SalesmenCategoryID ";

            queryString = queryString + "       FETCH NEXT FROM SalesmenCategory_Cursor INTO @SalesmenCategoryID ";

            queryString = queryString + "   END ";

            queryString = queryString + "   CLOSE SalesmenCategory_Cursor ";
            queryString = queryString + "   DEALLOCATE SalesmenCategory_Cursor ";

            SQLDatabase.CreateStoredProcedure("ListSalesmenCategoryUpdateHierarchyLevel", queryString);




            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[4];
            queryArray[0] = "   SELECT      TOP 1 SalesmenCategoryID FROM ListSalesmenCategory WHERE AncestorID = @FindIdentityID " ;
            queryArray[1] = "   SELECT      TOP 1 SalesmenCategoryID FROM ListSalesmenName WHERE SalesmenCategoryID = @FindIdentityID " ;
            queryArray[2] = "   SELECT      TOP 1 SalesmenCategoryID FROM InMarketMaster WHERE SalesmenCategoryID = @FindIdentityID " ;
            queryArray[3] = "   SELECT      TOP 1 SalesmenCategoryID FROM QuotaMaster WHERE SalesmenCategoryID = @FindIdentityID " ;

            SQLDatabase.CreateProcedureToCheckExisting("ListSalesmenCategoryEditable", queryArray);

        }
    }
}
